package com.example.demo2.service.impl;

import com.example.demo2.entity.Result;
import com.example.demo2.entity.UploadedData;
import com.example.demo2.mapper.ResultMapper;
import com.example.demo2.mapper.UploadedDataMapper;
import com.example.demo2.service.ExcelService;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

/**
 * @author
 * @version 1.0
 * @description TODO
 * @date 2024/7/1 9:59
 */
@Service
public class ExcelServiceImpl implements ExcelService {
    @Autowired
    private UploadedDataMapper uploadedDataMapper;
    private static final Logger logger = LoggerFactory.getLogger(ExcelServiceImpl.class);

    @Override
    public void processExcel(MultipartFile file, String uploadName) throws Exception {
        logger.info("Processing excel file with upload name: {}", uploadName);
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            List<UploadedData> dataList = new ArrayList<>();

            // 查找表头行中的Molecular Weight和RT [min]列
            int molecularWeightColumn = -1;
            int rtMinColumn = -1;
            Row headerRow = sheet.getRow(0);

            for (Cell cell : headerRow) {
                if (cell.getStringCellValue().equalsIgnoreCase("Molecular Weight")) {
                    molecularWeightColumn = cell.getColumnIndex();
                } else if (cell.getStringCellValue().equalsIgnoreCase("RT [min]")) {
                    rtMinColumn = cell.getColumnIndex();
                }
            }

            if (molecularWeightColumn == -1 || rtMinColumn == -1) {
                throw new Exception("无法找到'Molecular Weight'或'RT [min]'列");
            }

            for (Row row : sheet) {
                if (row.getRowNum() == 0) {
                    continue; // 跳过表头行
                }

                Cell molecularWeightCell = row.getCell(molecularWeightColumn);
                Cell rtMinCell = row.getCell(rtMinColumn);

                if (molecularWeightCell == null || rtMinCell == null) {
                    continue; // 跳过空单元格行
                }

                BigDecimal molecularWeight = getBigDecimalValue(molecularWeightCell);
                BigDecimal rtMin = getBigDecimalValue(rtMinCell);

                if (molecularWeight != null && rtMin != null) {
                    dataList.add(new UploadedData(molecularWeight, rtMin, uploadName));
                } else {
                    logger.warn("Skipping row {} due to invalid data", row.getRowNum());
                }
            }

            // 保存molecular weight和RT min到数据库
            if (!dataList.isEmpty()) {
                uploadedDataMapper.insertUploadedDataBatch(dataList);
                logger.info("Inserted {} records into the database", dataList.size());
            } else {
                logger.warn("No valid data found in the Excel file");
            }
        }
    }

    private BigDecimal getBigDecimalValue(Cell cell) {
        switch (cell.getCellType()) {
            case NUMERIC:
                return new BigDecimal(String.valueOf(cell.getNumericCellValue()));
            case STRING:
                try {
                    return new BigDecimal(cell.getStringCellValue());
                } catch (NumberFormatException e) {
                    logger.warn("Unable to parse string value to BigDecimal: {}", cell.getStringCellValue());
                    return null;
                }
            default:
                logger.warn("Unexpected cell type: {}", cell.getCellType());
                return null;
        }
    }
}